package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

public class BSC_Report_PerformWork extends SvrProcess {

	/* Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	
	int p_AD_Client_ID, p_AD_Org_ID, p_C_Period_ID;
	
	String ResultPath;
	
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null) 
				;
			else if (name.equals("AD_Client_ID"))
				p_AD_Client_ID = para[i].getParameterAsInt();
			else if (name.equals("AD_Org_ID"))
				p_AD_Org_ID = para[i].getParameterAsInt();
			else if (name.equals("C_Period_ID"))
				p_C_Period_ID = para[i].getParameterAsInt();
			else 
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Report_PerformWork_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try { 
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 20) + (++pathI).toString() + ")";  
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" +(++pathI).toString() + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));
		
		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;

		//New Cell Style 
		CellStyle cs = wb.createCellStyle();
		cs.setBorderBottom((short)1);
		cs.setBorderLeft((short)1);
		cs.setBorderRight((short)1);
		cs.setBorderTop((short)1);
		Font f = wb.createFont();
		f.setFontHeight((short) 200);
		f.setFontName("Times New Roman");
		cs.setFont(f); 
		cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		cs.setWrapText(true);
		
		List<line> l1 = new ArrayList<line>();
		List<line> l2 = new ArrayList<line>();
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT 	hrpd.Name AS Block " 
								+ ", hrd.Name AS Department " 
								+ ", bp.C_BPartner_ID AS BPartner_ID "
								+ ", bp.Name AS BPartner " 
								+ ", (SELECT count(bclc.ValueNumber) FROM BSC_CardLine bclc WHERE bclc.BSC_Card_ID = bcl.BSC_Card_ID) AS KPDtotalCount "
//								+ ", round(bc.ValueNumber, 2) AS KPDtotalCount " 
								+ ", count(bcl.BSC_CardLine_ID) AS KPDcount " 
								+ ", round((sum(bcl.ValueNumber) / bc.ValueNumber * 100), 5) AS KPDpercentage " 
								+ ", bcl.Name AS KPDname " 
							+ "FROM BSC_Card bc " 
							+ "JOIN BSC_CardLine bcl ON bcl.BSC_Card_ID = bc.BSC_Card_ID " 
							+ "LEFT JOIN C_BPartner bp ON bp.C_BPartner_ID = bc.C_BPartner_ID " 
							+ "LEFT JOIN HR_Department hrd ON hrd.HR_Department_ID = (SELECT HR_Department_ID FROM HR_Employee hre WHERE hre.HR_Employee_ID = bc.C_BPartner_ID) " 
							+ "LEFT JOIN HR_Department hrpd ON hrpd.HR_Department_ID = (SELECT Parent_ID FROM AD_TreeNodeDep tnd WHERE tnd.Node_ID = hrd.HR_Department_ID) " 
							+ "WHERE bcl.ValueNumber < 1 " 
							+ "AND bc.AD_Client_ID = " + p_AD_Client_ID + " " 
							+ "AND bc.AD_Org_ID = " + p_AD_Org_ID + " "
							+ "AND (bc.C_Period_ID = " + p_C_Period_ID + " OR " + p_C_Period_ID + " IS NULL OR " + p_C_Period_ID + " = 0) " 
							+ "GROUP BY hrpd.Name, hrd.Name, bp.C_BPartner_ID, bp.Name, bc.ValueNumber, bcl.ValueNumber, bcl.Name, bcl.BSC_Card_ID " 
							+ "ORDER BY bp.Name ";
		
		try {
			ps = DB.prepareStatement(sql, null);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				l1.add(new line(rs.getString("Block"), rs.getString("Department"), rs.getInt("BPartner_ID"), rs.getString("BPartner"), rs.getFloat("KPDTotalCount"), rs.getInt("KPDCount"), rs.getFloat("KPDpercentage"), rs.getString("KPDname")));
			}
		} catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		} finally {
			DB.close(rs, ps);
			rs = null;
			ps = null;
		}
		
		int j = 0;
		for (line iLine : l1) {	
			if (l2.size() != 0) {
				if (iLine.bp_id == l2.get(j).bp_id) {
					l2.get(j).kpdCount += iLine.kpdCount;
					l2.get(j).kpdPercentage += iLine.kpdPercentage;
					l2.get(j).kpdName += ", " + iLine.kpdName;
				} else {
					l2.add(iLine); 
					j++; 
				}
			} else {
				l2.add(iLine);
			}
		}
		
		int row = 3;
		int no = 0;
		for(j = 0; j < l2.size(); j++) {
			R = sheet.createRow(row);
			for (int i = 0; i < 8; i++) {
				C = R.createCell(i);
				C.setCellStyle(cs);
			}
			
			sheet.getRow(row).getCell(0).setCellValue(++no);
			sheet.getRow(row).getCell(1).setCellValue(l2.get(j).block);
			sheet.getRow(row).getCell(2).setCellValue(l2.get(j).dep);
			sheet.getRow(row).getCell(3).setCellValue(l2.get(j).bp);
			sheet.getRow(row).getCell(4).setCellValue(l2.get(j).kpdTotalCount);
			sheet.getRow(row).getCell(5).setCellValue(l2.get(j).kpdCount);
//			sheet.getRow(row).getCell(6).setCellValue(l2.get(j).kpdPercentage);
			sheet.getRow(row).getCell(6).setCellFormula("(F" + (row+1) + "/E" + (row+1) + "*100)");
			sheet.getRow(row).getCell(7).setCellValue(l2.get(j).kpdName);
			
			row++;
		}
		
		//---------------------------------------------------------------
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));

		return Msg.translate(m_ctx, "Success");
	}
	
	class line {
		String block;
		String dep;
		int bp_id = 0;
		String bp;
		float kpdTotalCount = 0;
		int kpdCount = 0;
		float kpdPercentage = 0;
		String kpdName;
		
		line(String block1, String dep1, int bp_id1, String bp1, float kpdTotalCount1, int kpdCount1, float kpdPercentage1, String kpdName1) {
			this.block = block1;
			this.dep = dep1;
			this.bp_id = bp_id1;
			this.bp = bp1;
			this.kpdTotalCount = kpdTotalCount1;
			this.kpdCount = kpdCount1;
			this.kpdPercentage = kpdPercentage1;
			this.kpdName = kpdName1;
		}
	}
}
